package com.tcfvic.client.panels.reports;

import java.util.ArrayList;
import java.util.List;

import com.google.gwt.core.client.GWT;
import com.google.gwt.event.dom.client.ClickEvent;
import com.google.gwt.event.dom.client.ClickHandler;
import com.google.gwt.event.dom.client.MouseDownEvent;
import com.google.gwt.event.dom.client.MouseDownHandler;
import com.google.gwt.user.client.Window;
import com.google.gwt.user.client.rpc.AsyncCallback;
import com.google.gwt.user.client.ui.Button;
import com.google.gwt.user.client.ui.HTML;
import com.google.gwt.user.client.ui.HorizontalPanel;
import com.google.gwt.user.client.ui.ListBox;
import com.google.gwt.user.client.ui.TextBox;
import com.google.gwt.user.client.ui.VerticalPanel;
import com.google.gwt.user.client.ui.Widget;
import com.tcfvic.client.PersonService;
import com.tcfvic.client.PersonServiceAsync;
import com.tcfvic.client.ReportService;
import com.tcfvic.client.ReportServiceAsync;
import com.tcfvic.client.panels.widgets.SimpleMessagePopup;

public class PersonReports {

	private final ReportServiceAsync reportService = GWT
			.create(ReportService.class);
	private final PersonServiceAsync personService = GWT
			.create(PersonService.class);
	private ListBox suburbs = new ListBox();
	private List<String> personColumnNames = new ArrayList<String>();

	public PersonReports() {
		reportService
				.getPeopleRetrenchedColumnNames(new AsyncCallback<List<String>>() {
					@Override
					public void onFailure(Throwable caught) {
						// TODO Auto-generated method stub

					}

					@Override
					public void onSuccess(List<String> result) {
						for (String column : result) {
							personColumnNames.add(column);
						}

					}
				});
		personService.getAllSuburbs(new AsyncCallback<List<String>>() {
			@Override
			public void onSuccess(List<String> result) {
				for (String suburb : result) {
					suburbs.addItem(suburb, suburb);
				}
			}

			@Override
			public void onFailure(Throwable caught) {
			}
		});
	}

	public Widget getContent() {
		VerticalPanel contents = new VerticalPanel();
		contents.add(getAllPersonData());
		contents.add(new HTML("<br><hr>"));
		contents.add(getAllDataFormatReport());
		contents.add(new HTML("<br><hr>"));
		contents.add(getJSPData());;
		contents.add(new HTML("<br><hr>"));
		contents.add(getEmploymentData());
		contents.add(new HTML("<br><hr>"));
		contents.add(getCTETrainingAndExpenseSummary());
		contents.add(new HTML("<br><hr>"));
		contents.add(getSummaryReport());
		contents.add(new HTML("<br><hr>"));
		contents.add(getWorkerTabQuery());
		contents.add(new HTML("<br><hr>"));
		contents.add(getAllPersonDataWithTraining());
		contents.add(new HTML("<br><hr>"));
		contents.add(getPersonsAgedOver());
		contents.add(new HTML("<br><hr>"));
		contents.add(getPeopleInSuburb());
		contents.add(new HTML("<br><hr>"));
		contents.add(getPeopleRetrenched());
		contents.add(new HTML("<br><hr>"));
		contents.add(getPeopleWhoHaveTrained());
		contents.add(new HTML("<br><hr>"));
		contents.add(getPeopleJSP());
		contents.add(new HTML("<br><hr>"));
		contents.add(getPeopleEmploymentStatus());
		return contents;
	}

	private ListBox getColumnNames(List<String> columns) {
		ListBox lb = new ListBox();
		lb.addItem("", "");
		for (String str : columns) {
			lb.addItem(str.split("\\.")[1], str);
		}
		return lb;
	}

	private ListBox getWorkersWhoHaveUndertakenTraining() {
		ListBox lb = new ListBox();
		lb.addItem("Course name", "c.name");
		lb.addItem("In a specified year", "date_format(pc.trainingdate, '%Y')");
		lb.addItem("In a specified month (Full name eg April)", "date_format(pc.trainingdate, '%M')");
		lb.addItem("In a specified state", "r.state");
		lb.addItem("In a specified regional area within a state", "r.suburb");
		lb.addItem("From a specified company", "wc.name");
		lb.addItem("From a specified site within a company", "wc.site");
		lb.addItem("Age", "(datediff(sysdate(), p.dateofbirth) / 365)");
		lb.addItem("Who speak a specified language",
				"p.firstLanguage,p.secLanguage,p.thirdLanguage");
		lb.addItem("Accessing a specified funding source", "fs.sourcename");
		lb.addItem("At a specified RTO", "r.name");
		return lb;
	}

	private ListBox getCTECritera() {
		ListBox lb = new ListBox();
		lb.addItem("In a specified year", "date_format(e.date, '%Y')");
		lb.addItem("In a specified month (Full name eg April)", "date_format(e.date, '%M')");
		return lb;
	}

	private ListBox getPersonJSPSearchCriteria() {
		ListBox lb = new ListBox();
		lb.addItem("In a specified year", "date_format(pc.trainingdate, '%Y')");
		lb.addItem("In a specified month (Full name eg April)", "date_format(pc.trainingdate, '%M')");
		lb.addItem("In a specified state", "r.state");
		lb.addItem("In a specified regional area within a state", "r.suburb");
		lb.addItem("From a specified company", "wc.name");
		lb.addItem("From a specified site within a company", "wc.site");
		lb.addItem("Age", "(datediff(sysdate(), p.dateofbirth) / 365)");
		lb.addItem("Who speak a specified language",
				"p.firstLanguage,p.secLanguage,p.thirdLanguage");
		lb.addItem("Registered with specified JSAP", "j.name");
		return lb;
	}

	private ListBox getPersonFoundWorkCriteria() {
		ListBox lb = new ListBox();
		lb.addItem("In a specified year", "date_format(pc.trainingdate, '%Y')");
		lb.addItem("In a specified month (Full name eg April)", "date_format(pc.trainingdate, '%M')");
		lb.addItem("In a specified state", "r.state");
		lb.addItem("In a specified regional area within a state", "r.suburb");
		lb.addItem("From a specified company", "wc.name");
		lb.addItem("From a specified site within a company", "wc.site");
		lb.addItem("Age", "(datediff(sysdate(), p.dateofbirth) / 365)");
		lb.addItem("Who speak a specified language",
				"p.firstLanguage,p.secLanguage,p.thirdLanguage");
		lb.addItem("in a specified industry", "es.industry");
		lb
				.addItem(
						"Worktype(Permanent,Casual,Contract,Homework,Volunteer)",
						"");
		lb.addItem("Work situation(F/T, P/T, Apprenticeship, Traineeship)", "");
		lb
				.addItem(
						"Covered by (Enterprise agreement, Award, Common law contract, Don't know",
						"");
		return lb;
	}

	private ListBox getEqualities() {
		ListBox lb = new ListBox();
		lb.addItem("", "");
		lb.addItem("contains", " like ");
		lb.addItem("=", "=");
		lb.addItem("<", "<");
		lb.addItem("<=", "<=");
		lb.addItem(">", ">");
		lb.addItem(">=", ">=");
		lb.addItem("!=", "!=");
		return lb;
	}

	public Widget getPeopleWhoHaveTrained() {
		final VerticalPanel vp = new VerticalPanel();
		HorizontalPanel hp = new HorizontalPanel();
		hp.add(new HTML("Worker who have undertaken training:&nbsp;&nbsp;"));
		Button button = new Button("Add criteria");
		hp.add(button);
		button.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				HorizontalPanel hp = new HorizontalPanel();
				hp.add(new HTML("Column criteria:"));
				hp.add(getWorkersWhoHaveUndertakenTraining());
				hp.add(getEqualities());
				hp.add(new TextBox());
				vp.add(hp);
			}
		});

		hp.add(new HTML("&nbsp;&nbsp;"));
		button = new Button("Go");
		hp.add(button);
		button.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				List<String> where = new ArrayList<String>();
				try {
					for (int i = 1; i < vp.getWidgetCount(); i++) {
						HorizontalPanel hp = (HorizontalPanel) vp.getWidget(i);
						int columnInt = ((ListBox) hp.getWidget(1))
								.getSelectedIndex();
						String column = ((ListBox) hp.getWidget(1))
								.getValue(columnInt);

						columnInt = ((ListBox) hp.getWidget(2))
								.getSelectedIndex();
						String equality = ((ListBox) hp.getWidget(2))
								.getValue(columnInt);

						String value = ((TextBox) hp.getWidget(3)).getText();

						generateWhere(where, column, equality, value);
					}
					reportService.getPeopleTrainingQuery(where,
							new ASyncReportCallBack());
				} catch (Exception e) {
					new SimpleMessagePopup("Error in query: " + e, null)
							.showPopup();
				}
			}

		});

		vp.add(hp);
		return vp;
	}

	public Widget getPeopleJSP() {
		final VerticalPanel vp = new VerticalPanel();
		HorizontalPanel hp = new HorizontalPanel();
		hp
				.add(new HTML(
						"Workers who have registered with a JSAP:&nbsp;&nbsp;"));
		Button button = new Button("Add criteria");
		hp.add(button);
		button.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				HorizontalPanel hp = new HorizontalPanel();
				hp.add(new HTML("Column criteria:"));
				hp.add(getPersonJSPSearchCriteria());
				hp.add(getEqualities());
				hp.add(new TextBox());
				vp.add(hp);
			}
		});

		hp.add(new HTML("&nbsp;&nbsp;"));
		button = new Button("Go");
		hp.add(button);
		button.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				List<String> where = new ArrayList<String>();
				try {
					for (int i = 1; i < vp.getWidgetCount(); i++) {
						HorizontalPanel hp = (HorizontalPanel) vp.getWidget(i);
						int columnInt = ((ListBox) hp.getWidget(1))
								.getSelectedIndex();
						String column = ((ListBox) hp.getWidget(1))
								.getValue(columnInt);

						columnInt = ((ListBox) hp.getWidget(2))
								.getSelectedIndex();
						String equality = ((ListBox) hp.getWidget(2))
								.getItemText(columnInt);

						String value = ((TextBox) hp.getWidget(3)).getText();

						generateWhere(where, column, equality, value);
					}
					reportService.getPeopleJSPQuery(where,
							new ASyncReportCallBack());
				} catch (Exception e) {
					new SimpleMessagePopup("Error in query: " + e, null)
							.showPopup();
				}
			}

		});

		vp.add(hp);
		return vp;
	}

	public Widget getPeopleEmploymentStatus() {
		final VerticalPanel vp = new VerticalPanel();
		HorizontalPanel hp = new HorizontalPanel();
		hp.add(new HTML("Workers who have found work:&nbsp;&nbsp;"));
		Button button = new Button("Add criteria");
		hp.add(button);
		button.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				HorizontalPanel hp = new HorizontalPanel();
				hp.add(new HTML("Column criteria:"));
				hp.add(getPersonFoundWorkCriteria());
				hp.add(getEqualities());
				hp.add(new TextBox());
				vp.add(hp);
			}
		});

		hp.add(new HTML("&nbsp;&nbsp;"));
		button = new Button("Go");
		hp.add(button);
		button.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				List<String> where = new ArrayList<String>();
				try {
					for (int i = 1; i < vp.getWidgetCount(); i++) {
						HorizontalPanel hp = (HorizontalPanel) vp.getWidget(i);
						int columnInt = ((ListBox) hp.getWidget(1))
								.getSelectedIndex();
						String column = ((ListBox) hp.getWidget(1))
								.getValue(columnInt);

						columnInt = ((ListBox) hp.getWidget(2))
								.getSelectedIndex();
						String equality = ((ListBox) hp.getWidget(2))
								.getItemText(columnInt);

						String value = ((TextBox) hp.getWidget(3)).getText();

						generateWhere(where, column, equality, value);
					}
					reportService.getPeopleRegisteredForWork(where,
							new ASyncReportCallBack());
				} catch (Exception e) {
					new SimpleMessagePopup("Error in query: " + e, null)
							.showPopup();
				}
			}

		});

		vp.add(hp);
		return vp;
	}

	private void generateWhere(List<String> where, String column,
			String equality, String value) {
		if (!column.isEmpty() && !equality.isEmpty() && !value.isEmpty()) {
			if (!where.isEmpty()) {
				where.add(" and ");
			}
			if (!column.contains("date_format") && column.split(",").length > 0) {
				int count = 0;
				where.add(" ( ");
				for (String str : column.split(",")) {
					if (count > 0) {
						where.add(" or ");
					}
					if (equality.contains("like")) {
						where.add(str + " " + equality + " '%" + value + "%'");
					} else {
						where.add(str + " " + equality + " '" + value + "'");
					}
					count++;
				}
				where.add(" ) ");
			} else {
				if (equality.contains("like")) {
					where.add(column + " " + equality + " '%" + value + "%'");
				} else {
					where.add(column + " " + equality + " '" + value + "'");
				}
			}
		}
	}

	public Widget getPeopleRetrenched() {
		final VerticalPanel vp = new VerticalPanel();
		HorizontalPanel hp = new HorizontalPanel();
		hp.add(new HTML("Workers retrenched:&nbsp;&nbsp;"));
		Button button = new Button("Add criteria");
		hp.add(button);
		button.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				HorizontalPanel hp = new HorizontalPanel();
				hp.add(new HTML("Column criteria:"));
				hp.add(getColumnNames(personColumnNames));
				hp.add(getEqualities());
				hp.add(new TextBox());
				vp.add(hp);
			}
		});
		hp.add(new HTML("&nbsp;&nbsp;"));
		button = new Button("Go");
		hp.add(button);
		button.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				List<String> where = new ArrayList<String>();
				try {
					for (int i = 1; i < vp.getWidgetCount(); i++) {
						HorizontalPanel hp = (HorizontalPanel) vp.getWidget(i);
						int columnInt = ((ListBox) hp.getWidget(1))
								.getSelectedIndex();
						String column = ((ListBox) hp.getWidget(1))
								.getValue(columnInt);

						columnInt = ((ListBox) hp.getWidget(2))
								.getSelectedIndex();
						String equality = ((ListBox) hp.getWidget(2))
								.getItemText(columnInt);

						String value = ((TextBox) hp.getWidget(3)).getText();

						if (!column.isEmpty() && !equality.isEmpty()
								&& !value.isEmpty()) {
							if (!where.isEmpty()) {
								where.add(" and ");
							}
							where.add(column + " " + equality + " '" + value
									+ "'");
						}
					}
					reportService.getPeopleDynamicQuery(where,
							new ASyncReportCallBack());
				} catch (Exception e) {
					new SimpleMessagePopup("Error in query: " + e, null)
							.showPopup();
				}
			}
		});

		vp.add(hp);
		return vp;
	}

	public Widget getPeopleInSuburb() {
		HorizontalPanel hp = new HorizontalPanel();
		hp.add(new HTML("How many people live in suburb: "));
		hp.add(suburbs);
		Button button = new Button("Go");
		hp.add(button);
		button.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				try {
					reportService.getPeopleInSuburb(suburbs.getValue(suburbs
							.getSelectedIndex()), new ASyncReportCallBack());
				} catch (Exception e) {
					new SimpleMessagePopup(
							"Please enter a valid number for age", null)
							.showPopup();
				}
			}
		});
		return hp;
	}

	private Widget getAllPersonData() {
		HTML link = new HTML("Download all person data");
		link.addMouseDownHandler(new MouseDownHandler() {
			@Override
			public void onMouseDown(MouseDownEvent event) {
				// TODO Auto-generated method stub
				reportService.getAllPersonDetails(new ASyncReportCallBack());
			}
		});
		return link;
	}

	private Widget getJSPData() {
		HTML link = new HTML("Download JSP Data");
		link.addMouseDownHandler(new MouseDownHandler() {
			@Override
			public void onMouseDown(MouseDownEvent event) {
				// TODO Auto-generated method stub
				reportService.getJSPData(new ASyncReportCallBack());
			}
		});
		return link;
	}

	private Widget getEmploymentData() {
		HTML link = new HTML("Download Employment Status Data");
		link.addMouseDownHandler(new MouseDownHandler() {
			@Override
			public void onMouseDown(MouseDownEvent event) {
				// TODO Auto-generated method stub
				reportService.getEmploymentData(new ASyncReportCallBack());
			}
		});
		return link;
	}

	private Widget getAllDataFormatReport() {
		HTML link = new HTML("Download as \"All Data\" spreadsheet");
		link.addMouseDownHandler(new MouseDownHandler() {
			@Override
			public void onMouseDown(MouseDownEvent event) {
				// TODO Auto-generated method stub
				reportService.getAllData(new ASyncReportCallBack());
			}
		});
		return link;
	}
	
	private Widget getCTETrainingAndExpenseSummary() {
		final VerticalPanel vp = new VerticalPanel();
		HorizontalPanel hp = new HorizontalPanel();
		hp.add(new HTML("Download CTE Training and Expense Summary:&nbsp;&nbsp;"));
		Button button = new Button("Add criteria");
		hp.add(button);
		button.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				HorizontalPanel hp = new HorizontalPanel();
				hp.add(new HTML("Column criteria:"));
				hp.add(getCTECritera());
				hp.add(getEqualities());
				hp.add(new TextBox());
				vp.add(hp);
			}
		});

		hp.add(new HTML("&nbsp;&nbsp;"));
		button = new Button("Go");
		hp.add(button);
		button.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				List<String> where = new ArrayList<String>();
				try {
					for (int i = 1; i < vp.getWidgetCount(); i++) {
						HorizontalPanel hp = (HorizontalPanel) vp.getWidget(i);
						int columnInt = ((ListBox) hp.getWidget(1))
								.getSelectedIndex();
						String column = ((ListBox) hp.getWidget(1))
								.getValue(columnInt);

						columnInt = ((ListBox) hp.getWidget(2))
								.getSelectedIndex();
						String equality = ((ListBox) hp.getWidget(2))
								.getValue(columnInt);

						String value = ((TextBox) hp.getWidget(3)).getText();

						generateWhere(where, column, equality, value);
					}
					reportService.getCTETrainingAndExpenseSummary(where,
							new ASyncReportCallBack());
				} catch (Exception e) {
					new SimpleMessagePopup("Error in query: " + e, null)
							.showPopup();
				}
			}

		});

		vp.add(hp);
		return vp;
	}

	
	private Widget getSummaryReport() {
		final VerticalPanel vp = new VerticalPanel();
		HorizontalPanel hp = new HorizontalPanel();
		hp.add(new HTML("Download course/expenses summary report:&nbsp;&nbsp;"));
		Button button = new Button("Add criteria");
		hp.add(button);
		button.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				HorizontalPanel hp = new HorizontalPanel();
				hp.add(new HTML("Column criteria:"));
				hp.add(getCTECritera());
				hp.add(getEqualities());
				hp.add(new TextBox());
				vp.add(hp);
			}
		});

		hp.add(new HTML("&nbsp;&nbsp;"));
		button = new Button("Go");
		hp.add(button);
		button.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				List<String> where = new ArrayList<String>();
				try {
					for (int i = 1; i < vp.getWidgetCount(); i++) {
						HorizontalPanel hp = (HorizontalPanel) vp.getWidget(i);
						int columnInt = ((ListBox) hp.getWidget(1))
								.getSelectedIndex();
						String column = ((ListBox) hp.getWidget(1))
								.getValue(columnInt);

						columnInt = ((ListBox) hp.getWidget(2))
								.getSelectedIndex();
						String equality = ((ListBox) hp.getWidget(2))
								.getValue(columnInt);

						String value = ((TextBox) hp.getWidget(3)).getText();

						generateWhere(where, column, equality, value);
					}
					reportService.getSummaryReport(where,
							new ASyncReportCallBack());
				} catch (Exception e) {
					new SimpleMessagePopup("Error in query: " + e, null)
							.showPopup();
				}
			}

		});

		vp.add(hp);
		return vp;
	}

	private Widget getWorkerTabQuery() {
		final VerticalPanel vp = new VerticalPanel();
		HorizontalPanel hp = new HorizontalPanel();
		hp.add(new HTML("Download all worker info:&nbsp;&nbsp;"));
		Button button = new Button("Add criteria");
		hp.add(button);
		button.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				HorizontalPanel hp = new HorizontalPanel();
				hp.add(new HTML("Column criteria:"));
				hp.add(getCTECritera());
				hp.add(getEqualities());
				hp.add(new TextBox());
				vp.add(hp);
			}
		});

		hp.add(new HTML("&nbsp;&nbsp;"));
		button = new Button("Go");
		hp.add(button);
		button.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				List<String> where = new ArrayList<String>();
				try {
					for (int i = 1; i < vp.getWidgetCount(); i++) {
						HorizontalPanel hp = (HorizontalPanel) vp.getWidget(i);
						int columnInt = ((ListBox) hp.getWidget(1))
								.getSelectedIndex();
						String column = ((ListBox) hp.getWidget(1))
								.getValue(columnInt);

						columnInt = ((ListBox) hp.getWidget(2))
								.getSelectedIndex();
						String equality = ((ListBox) hp.getWidget(2))
								.getValue(columnInt);

						String value = ((TextBox) hp.getWidget(3)).getText();

						generateWhere(where, column, equality, value);
					}
					reportService.getWorkerTabQuery(where,
							new ASyncReportCallBack());
				} catch (Exception e) {
					new SimpleMessagePopup("Error in query: " + e, null)
							.showPopup();
				}
			}

		});

		vp.add(hp);
		return vp;
	}

	private Widget getAllPersonDataWithTraining() {
		HTML link = new HTML("Download all person data with training");
		link.addMouseDownHandler(new MouseDownHandler() {
			@Override
			public void onMouseDown(MouseDownEvent event) {
				// TODO Auto-generated method stub
				reportService.getAllPersonWithTrainingDetails(new ASyncReportCallBack());
			}
		});
		return link;
	}

	private Widget getPersonsAgedOver() {
		HorizontalPanel hp = new HorizontalPanel();
		hp.add(new HTML("How many people are aged over"));
		final TextBox textInput = new TextBox();
		hp.add(textInput);
		Button button = new Button("Go");
		hp.add(button);
		button.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				// TODO Auto-generated method stub
				try {
					int year = Integer.parseInt(textInput.getText());
					reportService.getAgedOverXYears(year,
							new ASyncReportCallBack());
				} catch (Exception e) {
					new SimpleMessagePopup(
							"Please enter a valid number for age", null)
							.showPopup();
				}
			}
		});
		return hp;
	}

	private void handleError(Throwable caught) {
		GWT.log("Error occured getting all person data", caught);
		new SimpleMessagePopup("Error occured getting all person data", null)
				.showPopup();
	}

	/**
	 * Private callback method.
	 * 
	 * @author Dave
	 * 
	 */
	private class ASyncReportCallBack implements AsyncCallback<String> {
		@Override
		public void onFailure(Throwable caught) {
			handleError(caught);
		}

		@Override
		public void onSuccess(String result) {
			Window.open(result, "", "");
		}
	}
}
